﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.ETL.Domain.Services
{
    public class PubSourceService
    {
        public ServiceResult DeleteSource(Guid sourceId)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                Entities.EPubDataSource source = pdsRep.Get(p => p.Id == sourceId);

                if(source == null)
                {
                    return new ServiceResult(true, ErrorCode.COM_OBJNotExist, ErrorCode.String(ErrorCode.COM_OBJNotExist));
                }

                pdsRep.Delete(source);
                try
                {
                    dbContext.SaveChanges();
                    return new ServiceResult(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError));
                }
                catch(Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("删除数据源:{0}失败,Ex:{1}", sourceId, ex));
                    return new ServiceResult(false, ErrorCode.COM_Exception, ErrorCode.String(ErrorCode.COM_Exception));
                }
            }
        }

        public ServiceResult<Entities.EPubDataSource> Create(string userId, string name, int type, string value, string description, string parameterSettings)
        {
            if(string.IsNullOrEmpty(parameterSettings))
            {
                return new ServiceResult<Entities.EPubDataSource>(false, ErrorCode.PubDataSource_ErrorParameterSettings, ErrorCode.String(ErrorCode.PubDataSource_ErrorParameterSettings));
            }

            string[] parameterArr = parameterSettings.Split(new char[] { ',' });
            if(parameterArr.Length / 3 <= 0
                && parameterArr.Length % 3 != 0)
            {
                return new ServiceResult<Entities.EPubDataSource>(false, ErrorCode.PubDataSource_ErrorParameterSettings, ErrorCode.String(ErrorCode.PubDataSource_ErrorParameterSettings));
            }

            using(EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                Entities.EPubDataSource ePubSet = new Entities.EPubDataSource();
                ePubSet.Id = Guid.NewGuid();
                ePubSet.Name = name;
                ePubSet.Description = description;
                ePubSet.Value = value;
                ePubSet.Type = type;
                ePubSet.CreatedTime = DateTime.Now;
                ePubSet.Creator = userId;

                pdsRep.Create(ePubSet);

                EntityRepository<Entities.EPubParameter> pdspRep = new EntityRepository<Entities.EPubParameter>(dbContext);
                for (int index = 0; index < parameterArr.Length; index += 3)
                {
                    Entities.EPubParameter eParameter = new Entities.EPubParameter();
                    eParameter.Id = Guid.NewGuid();
                    eParameter.Name = parameterArr[index];
                    eParameter.Type = parameterArr[index + 1];
                    eParameter.Description = parameterArr[index + 2];
                    eParameter.DataSourceId = ePubSet.Id;
                    eParameter.Index = index / 3;

                    pdspRep.Create(eParameter);
                }

                try
                {
                    dbContext.SaveChanges();

                    return new ServiceResult<Entities.EPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), ePubSet);
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("创建数据服务数据集:{0},出错,ex:{1}", name, ex));

                    return new ServiceResult<Entities.EPubDataSource>(false, ErrorCode.COM_Exception, ErrorCode.String(ErrorCode.COM_Exception));
                }
            }
        }

        public ServiceResult Update(Guid id, int type, string value, string description, string parameterSettings)
        {
            if (string.IsNullOrEmpty(parameterSettings))
            {
                return new ServiceResult(false, ErrorCode.PubDataSource_ErrorParameterSettings, ErrorCode.String(ErrorCode.PubDataSource_ErrorParameterSettings));
            }

            string[] parameterArr = parameterSettings.Split(new char[] { ',' });
            if (parameterArr.Length / 3 <= 0
                && parameterArr.Length % 3 != 0)
            {
                return new ServiceResult(false, ErrorCode.PubDataSource_ErrorParameterSettings, ErrorCode.String(ErrorCode.PubDataSource_ErrorParameterSettings));
            }

            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                Entities.EPubDataSource ePubSet = pdsRep.Get(p => p.Id == id);

                if(ePubSet ==null)
                {
                    return new ServiceResult(false, ErrorCode.COM_OBJNotExist, ErrorCode.String(ErrorCode.COM_OBJNotExist));
                }

                ePubSet.Description = description;
                ePubSet.Value = value;
                ePubSet.Type = type;

                pdsRep.Update(ePubSet);

                dbContext.Database.ExecuteSqlCommand("delete from ETL_PubParameter where dataSourceid={0}", id);

                EntityRepository<Entities.EPubParameter> pdspRep = new EntityRepository<Entities.EPubParameter>(dbContext);
                for (int index = 0; index < parameterArr.Length; index += 3)
                {
                    Entities.EPubParameter eParameter = new Entities.EPubParameter();
                    eParameter.Id = Guid.NewGuid();
                    eParameter.Name = parameterArr[index];
                    eParameter.Type = parameterArr[index + 1];
                    eParameter.Description = parameterArr[index + 2];
                    eParameter.DataSourceId = ePubSet.Id;
                    eParameter.Index = index / 3;

                    pdspRep.Create(eParameter);
                }

                try
                {
                    dbContext.SaveChanges();

                    return new ServiceResult(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError));
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("更新数据服务数据集:{0},出错,ex:{1}", id, ex));

                    return new ServiceResult(false, ErrorCode.COM_Exception, ErrorCode.String(ErrorCode.COM_Exception));
                }
            }
        }

        public PagedList<Entities.EPubDataSource> GetPagedList(string filter, int start, int limit)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                if (string.IsNullOrEmpty(filter))
                {
                    List<Entities.EPubDataSource> list = pdsRep.Query().OrderBy(p => p.Name).Take(limit).Skip(start).ToList();

                    int total = pdsRep.Count();

                    return new PagedList<Entities.EPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), start, limit, total, list);
                }
                else
                {
                    List<Entities.EPubDataSource> list = pdsRep.Query(p=>p.Name.Contains(filter) || p.Value.Contains(filter)).OrderBy(p => p.Name).Take(limit).Skip(start).ToList();

                    int total = pdsRep.Count(p => p.Name.Contains(filter) || p.Value.Contains(filter));

                    return new PagedList<Entities.EPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), start, limit, total, list);
                }
            }
        }

        /// <summary>
        /// 获取发布的数据源列表
        /// </summary>
        /// <returns></returns>
        public ListResult<Entities.EPubDataSource> GetList()
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                List<Entities.EPubDataSource> list = pdsRep.Query().OrderBy(p => p.Name).ToList();

                return new ListResult<Entities.EPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), list);
            }
        }

        public ListResult<Entities.EPubParameter> GetParameters(Guid dataSourceId)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubParameter> pdspRep = new EntityRepository<Entities.EPubParameter>(dbContext);

                List<Entities.EPubParameter> list = pdspRep.Query(p => p.DataSourceId == dataSourceId).OrderBy(p => p.Index).ToList();
                return new ListResult<Entities.EPubParameter>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), list);
            }
        }

        public ServiceResult<Entities.EPubDataSource> Get(Guid id)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                Entities.EPubDataSource ePubDataSource = pdsRep.Get(p => p.Id == id);

                if(ePubDataSource == null)
                {
                    return new ServiceResult<Entities.EPubDataSource>(false, ErrorCode.COM_OBJNotExist, ErrorCode.String(ErrorCode.COM_OBJNotExist));
                }

                return new ServiceResult<Entities.EPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), ePubDataSource);
            }
        }

        public ServiceResult<Views.VPubDataSource> GetDetail(Guid id)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);

                EntityRepository<Entities.EPubParameter> pdspRep = new EntityRepository<Entities.EPubParameter>(dbContext);

                Entities.EPubDataSource ePubDataSet = pdsRep.Get(p => p.Id == id);

                if (ePubDataSet == null)
                {
                    return new ServiceResult<Views.VPubDataSource>(false, ErrorCode.COM_OBJNotExist, ErrorCode.String(ErrorCode.COM_OBJNotExist));
                }

                Views.VPubDataSource vPubDataSet = new Views.VPubDataSource();
                vPubDataSet.Id = ePubDataSet.Id;
                vPubDataSet.Type = ePubDataSet.Type;
                vPubDataSet.Value = ePubDataSet.Value;
                vPubDataSet.Description = ePubDataSet.Description;
                vPubDataSet.Name = ePubDataSet.Name;

                vPubDataSet.Parameters = pdspRep.Query(p => p.DataSourceId == id).OrderBy(p => p.Index).ToList();
                return new ServiceResult<Views.VPubDataSource>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), vPubDataSet);
            }
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="dataSourceId"></param>
        /// <param name="parameterNames"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public ServiceResult<System.Data.DataTable> GetData(Guid dataSourceId, List<string> parameterNames, List<string> parameterValues)
        {
            using (EtlDbContext dbContext = new EtlDbContext())
            {
                EntityRepository<Entities.EPubDataSource> pdsRep = new EntityRepository<Entities.EPubDataSource>(dbContext);
                EntityRepository<Entities.EPubParameter> pdspRep = new EntityRepository<Entities.EPubParameter>(dbContext);

                Entities.EPubDataSource ePubDataSet = pdsRep.Get(p => p.Id == dataSourceId);
                if(ePubDataSet == null)
                {
                    return new ServiceResult<System.Data.DataTable>(false, ErrorCode.COM_OBJNotExist, ErrorCode.String(ErrorCode.COM_OBJNotExist));
                }

                List<Entities.EPubParameter> parameters = pdspRep.GetList(p => p.DataSourceId == dataSourceId);

                SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
                SqlCommand sqlCommand = new SqlCommand();
                if(ePubDataSet.Type == DEF.PubDataSetType.Statement)
                {
                    // sql statement

                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = ePubDataSet.Value;
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                }
                else
                {
                    // storedProcedure

                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = ePubDataSet.Value;
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                }


                for (int index = 0; index < parameterNames.Count; index++)
                {
                    SqlParameter sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = parameterNames[index];
                    sqlParameter.Value = parameterValues[index];
                    sqlCommand.Parameters.Add(sqlParameter);
                }

                System.Data.DataTable dataTable = new System.Data.DataTable();
                dataTable.TableName = "PubSourceData";
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
                sqlAdapter.Fill(dataTable);

                return new ServiceResult<System.Data.DataTable>(true, ErrorCode.COM_NoError, ErrorCode.String(ErrorCode.COM_NoError), dataTable);
            }
        }
    }
}
